﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using DoAnWeb.DTO;

namespace DoAnWeb.DAO
{
    public class TaiKhoanDAO
    {
        DataProvider dp = new DataProvider();
        
        public BindingList<TaiKhoanDTO> GetList()
        {
            if (dp.OpenConnect())
            {
                DataTable dt = dp.SqlExcuteQuery("uproc_GetList_TaiKhoan", null);
                dp.CloseConnect();
                BindingList<TaiKhoanDTO> List = new BindingList<TaiKhoanDTO>();
                foreach (DataRow row in dt.Rows)
                {
                    TaiKhoanDTO DTO = new TaiKhoanDTO();
                    DTO.TenTaiKhoan = row["TenTaiKhoan"].ToString();
                    DTO.MatKhau = row["MatKhau"].ToString();
                    DTO.HoTen = row["HoTen"].ToString();
                    DTO.TenDoanhNghiep = row["TenDoanhNghiep"].ToString();
                    DTO.HinhAnh = row["HinhAnh"].ToString();
                    DTO.NgaySinh = DateTime.Parse(row["NgaySinh"].ToString());
                    if (int.Parse(row["GioiTinh"].ToString()) == 0)
                    {
                        DTO.GioiTinh = "Nữ";
                    }
                    else
                    {
                        DTO.GioiTinh = "Nam";
                    }
                    DTO.DiaChi = row["DiaChi"].ToString();
                    DTO.DienThoai = row["DienThoai"].ToString();
                    DTO.Email = row["Email"].ToString();
                    DTO.MaLoaiTaiKhoan = row["MaLoaiTaiKhoan"].ToString();
                    DTO.TenLoaiTaiKhoan = row["TenLoaiTaiKhoan"].ToString();
                    DTO.NgayDangKy = DateTime.Parse(row["NgayDangKy"].ToString());
                    if (int.Parse(row["DaXoa"].ToString()) == 0)
                    {
                        DTO.DaXoa = "Bình thường";
                    }
                    else
                    {
                        DTO.DaXoa = "Đã Xóa";
                    }
                    List.Add(DTO);
                }
                return List;
            }
            return null;
        }

        public TaiKhoanDTO GetFromId(string _TenTaiKhoan)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@TenTaiKhoan", _TenTaiKhoan) };
                DataTable dt = dp.SqlExcuteQuery("uproc_GetFromID_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                TaiKhoanDTO DTO = new TaiKhoanDTO();
                foreach (DataRow row in dt.Rows)
                {
                    DTO.TenTaiKhoan = row["TenTaiKhoan"].ToString();
                    DTO.MatKhau = row["MatKhau"].ToString();
                    DTO.HoTen = row["HoTen"].ToString();
                    DTO.TenDoanhNghiep = row["TenDoanhNghiep"].ToString();
                    DTO.HinhAnh = row["HinhAnh"].ToString();
                    DTO.NgaySinh = DateTime.Parse(row["NgaySinh"].ToString());
                    if (int.Parse(row["GioiTinh"].ToString()) == 0)
                    {
                        DTO.GioiTinh = "Nữ";
                    }
                    else
                    {
                        DTO.GioiTinh = "Nam";
                    }
                    DTO.DiaChi = row["DiaChi"].ToString();
                    DTO.DienThoai = row["DienThoai"].ToString();
                    DTO.Email = row["Email"].ToString();
                    DTO.MaLoaiTaiKhoan = row["MaLoaiTaiKhoan"].ToString();
                    DTO.TenLoaiTaiKhoan = row["TenLoaiTaiKhoan"].ToString();
                    DTO.NgayDangKy = DateTime.Parse(row["NgayDangKy"].ToString());
                    if (int.Parse(row["DaXoa"].ToString()) == 0)
                    {
                        DTO.DaXoa = "Bình thường";
                    }
                    else
                    {
                        DTO.DaXoa = "Đã Xóa";
                    }
                }
                return DTO;
            }
            return null;
        }

        public TaiKhoanDTO GetPassword(string _TenTaiKhoan)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@TenTaiKhoan", _TenTaiKhoan) };
                DataTable dt = dp.SqlExcuteQuery("uproc_GetMatKhau_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                TaiKhoanDTO DTO = new TaiKhoanDTO();
                foreach (DataRow row in dt.Rows)
                {
                    DTO.MatKhau = row["MatKhau"].ToString();
                }
                return DTO;
            }
            return null;
        }

        public string GetKind(string _TenTaiKhoan)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@TenTaiKhoan", _TenTaiKhoan) };
                DataTable dt = dp.SqlExcuteQuery("uproc_GetFromID_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                TaiKhoanDTO DTO = new TaiKhoanDTO();
                foreach (DataRow row in dt.Rows)
                {
                    DTO.MaLoaiTaiKhoan = row["MaLoaiTaiKhoan"].ToString();
                }
                return DTO.MaLoaiTaiKhoan;
            }
            return null ;
        }

        public bool CheckExist(string _TenTaiKhoan ,string _MatKhau)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@TenTaiKhoan", _TenTaiKhoan), 
                    new SqlParameter("@MatKhau", _MatKhau)
                };
                DataTable dt = dp.SqlExcuteQuery("uproc_CheckExists_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                if (dt.Rows.Count <= 0)
                    return false;
                else
                    return true;
            }
            return false;
        }

        public bool Update_PassWord(string _TenTaiKhoan,string _NewMatKhau)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@TenTaiKhoan", _TenTaiKhoan), 
                    new SqlParameter("@NewMatKhau", _NewMatKhau)
                };
                bool kq = dp.SqlExcuteNonQuery("uproc_Update_Password", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;
        }

        public bool CheckDelete(int _Ma)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@MaTaiKhoan", _Ma) };
                bool kq = dp.SqlExcuteNonQuery("uproc_CheckDelete_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;
        }

        public bool UnCheckDelete(int _Ma)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = { new SqlParameter("@MaTaiKhoan", _Ma) };
                bool kq = dp.SqlExcuteNonQuery("uproc_UnCheckDelete_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;
        }

        public bool Delete(int _Ma)
        {
            if (dp.OpenConnect())
            {
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@MaTaiKhoan", _Ma) 
                };
                bool kq = dp.SqlExcuteNonQuery("uproc_Delete_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;
        }
        
        public bool Add(TaiKhoanDTO _DTO)
        {
            if (dp.OpenConnect())
            {
                int GioiTinh = (_DTO.GioiTinh == "Nam") ? 1 : 0;
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@DiaChi", _DTO.DiaChi) ,
                    new SqlParameter("@DienThoai", _DTO.DienThoai) ,
                    new SqlParameter("@Email", _DTO.Email) ,
                    new SqlParameter("@GioiTinh", GioiTinh) ,
                    new SqlParameter("@HoTen", _DTO.HoTen) ,
                    new SqlParameter("@MaLoaiTaiKhoan", _DTO.MaLoaiTaiKhoan) ,
                    new SqlParameter("@MatKhau", _DTO.MatKhau) ,
                    new SqlParameter("@NgayDangKy", _DTO.NgayDangKy) ,
                    new SqlParameter("@NgaySinh", _DTO.NgaySinh) ,
                    new SqlParameter("@TenDoanhNghiep", _DTO.TenDoanhNghiep) ,
                    new SqlParameter("@TenTaiKhoan", _DTO.TenTaiKhoan) 
                };
                bool kq = dp.SqlExcuteNonQuery("uproc_Add_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;
        }

        public bool Update(TaiKhoanDTO _DTO)
        {
            if (dp.OpenConnect())
            {
                int GioiTinh = (_DTO.GioiTinh == "Nam") ? 1 : 0;
                SqlParameter[] sqlParameter = 
                { 
                    new SqlParameter("@TenTaiKhoan", _DTO.TenTaiKhoan) ,
                    new SqlParameter("@DiaChi", _DTO.DiaChi) ,
                    new SqlParameter("@DienThoai", _DTO.DienThoai) ,
                    new SqlParameter("@Email", _DTO.Email) ,
                    new SqlParameter("@GioiTinh", GioiTinh) ,
                    new SqlParameter("@HoTen", _DTO.HoTen) ,
                    new SqlParameter("@NgaySinh", _DTO.NgaySinh) ,
                    new SqlParameter("@TenDoanhNghiep", _DTO.TenDoanhNghiep) 
                };
                bool kq = dp.SqlExcuteNonQuery("uproc_Update_TaiKhoan", sqlParameter);
                dp.CloseConnect();
                return kq;
            }
            return false;
        }

    }
}